import json
import time
import random
from io import BytesIO

import xlsxwriter
from urllib.parse import unquote, quote
from apis import api_routes
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from models.logs.logs import Logs
from public.local import date_type, local_time
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import Union
from sqlalchemy import text
from sqlalchemy import distinct
from models.course.course import Course
from models.order.tasks import Tasks
from models.product.routes import Routes
from models.course.detailed import Detailed
from models.facility.facility import Facility
from models.extra.extra import Charge
from models.users.users import Users
from models.product.aircraft import Aircraft
from public.config import config


@api_routes.route("/board/output", methods=["GET"])
def func_board_output_get_api():
    """
    今天每小时产量统计，(报表展示接口)完成API开发
    http://127.0.0.1:8080/api/board/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 看板数据接口
    """
    mow_time: datetime = datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S"), "%Y-%m-%d %H:%M:%S")
    today: datetime = datetime.strptime(datetime.now().strftime('%Y-%m-%d'), "%Y-%m-%d")
    starts: list = []
    for index in range(0, 24, 2):
        interval: datetime = today + timedelta(hours=index)
        starts.append(interval)
    keys: list = []
    for index, itr in enumerate(starts):
        stop: datetime = itr + timedelta(hours=2)

        keys.append({
            "start": itr,
            "stop": stop
        })

    values: list = []
    for itr in keys:
        start: datetime = itr.get("start")
        stop: datetime = itr.get("stop")

        total: int = g.session.query(Course.id).filter(and_(
                        Course.finish >= start, Course.finish < stop
                    )).count()

        standard: int = g.session.query(Course.id).filter(and_(
                        Course.finish >= start, Course.finish < stop, Course.mark == 1
                    )).count()

        # values.append({
        #     "start": itr.get("start").strftime("%H:%M"),
        #     "stop": itr.get("stop").strftime("%H:%M"),
        #     "total": random.randint(1, 100) if config.DEVELOP else total,
        #     "standard":  random.randint(1, 100) if config.DEVELOP else standard,
        #     "pass": True if mow_time > stop else False
        # })
        values.append({
            "start": itr.get("start").strftime("%H:%M"),
            "stop": itr.get("stop").strftime("%H:%M"),
            "total": total,
            "standard": standard,
            "pass": True if mow_time > stop else False
        })
    docs: dict = {
        "start": "时间点开始时间",
        "stop": "时间点结束时间",
        "total": "总共生产数量",
        "standard": "合格数量",
        "pass": "以当前时间轴为参照物，以过时间节点为true，还没过为false"
    }
    return jsonify({'status': 200, 'data': values, "docs": docs})


@api_routes.route("/station/output/number", methods=["GET"])
def func_station_output_get_api():
    """
    新增  每小时产量统计，完成API开发
    http://127.0.0.1:8080/api/station/output/number
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         查询日期
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 工站每小时产能统计
    """
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    if ft_id is None:
        abort(code=400)
    r_date = time.strftime("%Y-%m-%d")
    if start is not None and len(start) == 10:
        r_date = start
    sql_context = """
    SELECT 
        (SELECT name FROM facility WHERE id = :ft_id),
        to_char(starts,'yyyy-MM-dd hh24:00:00') AS starts,
        to_char(starts + INTERVAL '1 hour' ,'yyyy-MM-dd hh24:00:00') AS finish, 
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND time >= starts AND time < starts  + INTERVAL '1 hour') as alls,
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND detailed.status = TRUE AND time >= starts AND time < starts  + INTERVAL '1 hour') as ok,
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND detailed.status = FALSE AND time >= starts AND time < starts  + INTERVAL '1 hour') as ng
    FROM
    (
    SELECT 
        intervals AS starts,
        TO_CHAR(intervals + INTERVAL '1 hour','YYYY-MM-DD HH24:MI:SS') AS finish
    FROM
    (
    WITH RECURSIVE generated_numbers AS (
        SELECT 0 AS indexs
        UNION ALL
        SELECT indexs + 1
        FROM generated_numbers
        WHERE indexs < 23 
    )
    SELECT 
        TO_TIMESTAMP(:r_date || ' ' || indexs || ':00:00', 'YYYY-MM-DD HH24:MI:SS') AS intervals
    FROM generated_numbers
    ) AS temp_sed 
    ) AS temp_th3
    """
    db_date: list = g.session.execute(text(sql_context), {"r_date": r_date, "ft_id": ft_id}).fetchall()
    values: list = []
    for itr in db_date:
        values.append({
            "ft_name": itr[0],
            "start": itr[1],
            "stop": itr[2],
            "total": itr[3],
            "standard": itr[4],
            "ng": itr[5],
        })
    docs: dict = {
        "start": "时间点开始时间",
        "stop": "时间点结束时间",
        "total": "总共生产数量",
        "standard": "合格数量",
        "ng": "NG数量"
    }
    return jsonify({'status': 200, 'data': values, "docs": docs})


@api_routes.route("/station/output/number/excel", methods=["GET"])
def func_station_output_excel_get_api():
    """
    新增  每小时产量统计，导出excel, 完成API开发
    http://127.0.0.1:8080/api/station/output/number/excel
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         查询日期
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 工站每小时产能统计
    """
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    if ft_id is None:
        abort(code=400)
    r_date = time.strftime("%Y-%m-%d")
    if start is not None and len(start) == 10:
        r_date = start
    sql_context = """
    SELECT 
        (SELECT name FROM facility WHERE id = :ft_id),
        to_char(starts,'yyyy-MM-dd hh24:00:00') AS starts,
        to_char(starts + INTERVAL '1 hour' ,'yyyy-MM-dd hh24:00:00') AS finish, 
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND time >= starts AND time < starts  + INTERVAL '1 hour') as alls,
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND detailed.status = TRUE AND time >= starts AND time < starts  + INTERVAL '1 hour') as ok,
        (SELECT COUNT(id) FROM detailed WHERE facility_id = :ft_id AND detailed.status = FALSE AND time >= starts AND time < starts  + INTERVAL '1 hour') as ng
    FROM
    (
    SELECT 
        intervals AS starts,
        TO_CHAR(intervals + INTERVAL '1 hour','YYYY-MM-DD HH24:MI:SS') AS finish
    FROM
    (
    WITH RECURSIVE generated_numbers AS (
        SELECT 0 AS indexs
        UNION ALL
        SELECT indexs + 1
        FROM generated_numbers
        WHERE indexs < 23 
    )
    SELECT 
        TO_TIMESTAMP(:r_date || ' ' || indexs || ':00:00', 'YYYY-MM-DD HH24:MI:SS') AS intervals
    FROM generated_numbers
    ) AS temp_sed 
    ) AS temp_th3
    """
    db_date: list = g.session.execute(text(sql_context), {"r_date": r_date, "ft_id": ft_id}).fetchall()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史运行状态数据表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "开始时间", "结束时间", "生产全部数量", "合格数量", "不合格数量"]
    worksheet_index.write_row(row=0, col=0, data=message)

    for index, itr in enumerate(db_date):
        sequence = index + 1
        worksheet_index.write_row(row=sequence, col=0, data=[itr[0], itr[1], itr[2], itr[3], itr[4], itr[5]])

    # 在数据流之前关闭工作薄
    workbook.close()

    file_name = "{}.xlsx".format(local_time())

    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/board/task", methods=["GET"])
# @certification(auth=True, role=None)
def func_board_task2_get_api():
    """
    报表订单信息，以及产能统计，该工单NG产量统计，完成API开发（返回值有可能为空）
    http://127.0.0.1:8080/api/board/task
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 看板数据接口
    """
    docs: dict = {
        "amount": "计划生产数量",
        "totals": "实际生产数",
        "actual": "合格数量",
        "aircraft": "机种名称",
        "ngs": {
            "serials": "设备编号",
            "name": "设备名字",
            "ngs": "NG个数"
        }
    }
    db_task: Tasks = g.session.query(Tasks).filter(Tasks.status == 't').first()
    routes_id: int = 0
    aircraft: Union[str, None] = None

    if db_task is not None:
        db_aircraft: Aircraft = g.session.query(Aircraft).filter(Aircraft.id == db_task.aircraft_id).first()
        routes_id = db_aircraft.route_id
        aircraft = db_aircraft.aircraft

    task_id: int = db_task.id if db_task else 0
    db_route: Routes = g.session.query(Routes).filter(Routes.id == routes_id).first()

    # data: dict = {
    #     "amount": db_task.amount if db_task else None,
    #     "totals": db_task.totals if db_task else None,
    #     "actual": db_task.actual if db_task else None,
    #     "aircraft": db_route.aircraft if db_route else None
    # }
    data: dict = {
        "amount": db_task.amount if db_task else None,
        "totals": db_task.totals if db_task else None,
        "actual": db_task.actual if db_task else None,
        "aircraft": aircraft
    }

    ngs: list = []
    ftd_ids: list = []
    if db_route is not None:
        db_routes: dict = json.loads(db_route.route)
        data_routes: list = db_routes.get("data")
        for itr in data_routes:
            second: list = itr.get("ft_ids")
            ftd_ids.extend(second)
    ftd_ids: list = list(set(ftd_ids))

    today_str = "{} 00:00:00".format(time.strftime("%Y-%m-%d"))
    sql_total = """
            SELECT 
                COALESCE(COUNT(courses.sequence), 0) 
            FROM courses WHERE courses.finish >= :today
        """
    sql_ng = """
            SELECT 
                COALESCE(COUNT(courses.sequence), 0) 
            FROM courses WHERE courses.finish >= :today AND courses.mark >= 2
        """
    all_total, = g.session.execute(text(sql_total), {"today": today_str}).fetchone()
    all_ngs, = g.session.execute(text(sql_ng), {"today": today_str}).fetchone()
    yields = {
        "all_total": all_total,
        "all_ng": all_ngs,
        "all_ok": all_total - all_ngs
    }

    sql_ngs: str = """
        SELECT 
            COALESCE(COUNT(detailed.sequence), 0) 
        FROM detailed INNER JOIN courses ON detailed.sequence = courses.sequence 
        WHERE detailed.status = 'f' AND courses.finish >= :tk_id 
        AND facility_id = :ft_id GROUP BY detailed.sequence
    """

    for index, itr in enumerate(ftd_ids):
        db_fty: Facility = g.session.query(Facility).filter(Facility.id == itr).first()
        count = g.session.execute(text(sql_ngs), {"tk_id": today_str, "ft_id": itr}).fetchone()

        ngs.append({
            "serials": db_fty.serials,
            "name": db_fty.name,
            "ngs": count[0] if count else 0
        })

    charges: dict = {
        "user1": None,
        "user2": None,
        "user3": None,
    }
    charge: Union[Charge, None] = g.session.query(Charge).filter(Charge.id == 1).first()
    if charge is not None:
        user: Users = g.session.query(Users).filter(Users.id == charge.user_id1).first()
        charges["user1"] = user.name

        user: Users = g.session.query(Users).filter(Users.id == charge.user_id2).first()
        charges["user2"] = user.name

        user: Users = g.session.query(Users).filter(Users.id == charge.user_id3).first()
        charges["user3"] = user.name

    return jsonify({'status': 200, 'data': data, "docs": docs, "ngs": ngs, "charges": charges, "yields": yields})
